package com.lyy.common.utils.excel;

import com.lyy.common.utils.DateUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URL;
import java.util.Date;
import java.util.List;


public class ImportExportUtils {


    /**
     * 通用方法
     *
     * @param object
     * @param clazz
     * @throws Exception
     */
    public static void export(HttpServletRequest request, HttpServletResponse response,
                              Object object, List<?> clazz) throws Exception {

        //sheetName，fileName生成时间表名
        String sheetName = DateUtils.formatDate(new Date()) + ".xls";
        String fileName = DateUtils.formatDate(new Date()) + ".xls";
        //从实体类中遍历所有属性
        Class cls = object.getClass();
        Field[] fields = cls.getDeclaredFields();
        Field f = null;
        StringBuilder t = null;
        for (Field field : fields) {
            f = field;
            t.append(f.getName()).append(",");
        }
        //将所有属性遍历出来放入数组中如：{"编号","供应商","材料名称","单位","数量","单价(元)","合计(元)","备注"}
        String[] title = t.toString().split(",");

        String[][] content = new String[clazz.size() + 1][title.length];
        //向数组里面塞东西
        for (int i = 0; i < clazz.size(); i++) {
            object = clazz.get(i);
            for (int j = 1; j < title.length; j++) {
                //拼接成get**
                String getMethodName = "get" + title[j].substring(0, 1).toUpperCase() + title[j].substring(1);
                //将拼接的get**动态获取方法
                Method getMethod = cls.getMethod(getMethodName);
                Object value = getMethod.invoke(object);
                content[i][0] = i + 1 + "";
                //判断要是为null值就为空
                if (null == value) {
                    value = "";
                }
                content[i][j] = value + "";
            }
        }
        //创建HSSFWorkbook
        HSSFWorkbook wb = ImportExportUtils.getHSSFWorkbook(sheetName, title, content, null);
        //响应到客户端
        try {
            ImportExportUtils.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 发送响应流方法
     *
     * @param response
     * @param fileName
     */
    public static void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }


    /**
     * 导出Excel
     *
     * @param sheetName sheet名称
     * @param title     标题
     * @param values    内容
     * @param wb        HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook wb) {
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
        wb.getSheet(sheetName).setDefaultColumnWidth((short) 20);

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }
        //创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < values[i].length; j++) {
                //将内容按顺序赋给对应的列对象
                row.createCell(j).getCellStyle().setWrapText(true);//自动换行
                row.createCell(j).getCellStyle().setAlignment(HorizontalAlignment.CENTER);//水平居中
                row.createCell(j).getCellStyle().setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
                if ("null".equals(values[i][j])) {
                    row.createCell(j).setCellValue("");
                } else {
                    row.createCell(j).setCellValue(values[i][j]);
                }
            }
        }
        return wb;
    }

    /**
     * 导出某个人所有月出勤，有的绿色，没有的红色
     *
     * @param sheetName
     * @param title
     * @param values
     * @param wb
     * @return
     */
    public static HSSFWorkbook getHSSFRedGread(String sheetName, String[] title, String[][] values, HSSFWorkbook wb) {
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
//            cell.setCellStyle(style);

        }

        //创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < values[i].length; j++) {
                //将内容按顺序赋给对应的列对象加颜色
                if (StringUtils.isNotBlank(values[i][j])) {
                    if ("×".equals(values[i][j])) {
                        CellStyle redStyle = wb.createCellStyle();
                        HSSFFont redFont = wb.createFont();
                        redFont.setColor(HSSFColor.RED.index);
                        redStyle.setFont(redFont);
                        HSSFCell cell1 = row.createCell(j);
                        cell1.setCellStyle(redStyle);
                        cell1.setCellValue(values[i][j]);
                    } else if ("√".equals(values[i][j].substring(0, 1))) {
                        CellStyle redStyle = wb.createCellStyle();
                        HSSFFont redFont = wb.createFont();
                        redFont.setColor(HSSFColor.GREEN.index);
                        redStyle.setFont(redFont);
                        HSSFCell cell1 = row.createCell(j);
                        cell1.setCellStyle(redStyle);
                        cell1.setCellValue(values[i][j]);
                    } else {
                        row.createCell(j).setCellValue(values[i][j]);
                    }
                }
            }
        }
        return wb;
    }


    /**
     * 导出未实名
     *
     * @param sheetName sheet名称
     * @param title     标题
     * @param values    内容
     * @param wb        HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook exportStranger(String sheetName, String[] title, String[][] values, HSSFWorkbook wb) throws Exception {
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        wb.getSheet("未登记人员体温统计").setDefaultRowHeight((short) 2400);
        sheet.setColumnWidth(3, 20 * 256);
        sheet.setColumnWidth(1, 20 * 256);
        style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中


        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }
        //创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < values[i].length; j++) {
                //将内容按顺序赋给对应的列对象
                if (1 == j && i + 1 > 1) {
                    if (StringUtils.isNotBlank(values[i - 1][1])) {
                        FileOutputStream fileOut = null;
                        BufferedImage bufferImg = null;
                        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                        URL url = new URL(values[i - 1][1]);
                        bufferImg = ImageIO.read(url);
                        ImageIO.write(bufferImg, "jpg", byteArrayOut);

                        //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
                        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                        //anchor主要用于设置图片的属性
                        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 1, i, (short) 2, i + 1);
                        //插入图片
                        patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                    }
                } else {
                    row = sheet.createRow(i + 1);
                    row.setHeight((short) 2400);//目的是想把行高设置成25px
                    cell = row.createCell(0);
                    cell.setCellStyle(style);
                    cell.setCellValue(values[i][0]);

                    cell = row.createCell(2);
                    cell.setCellStyle(style);
                    cell.setCellValue(values[i][2]);

                    cell = row.createCell(3);
                    cell.setCellStyle(style);
                    cell.setCellValue(values[i][3]);

                    cell = row.createCell(4);
                    cell.setCellStyle(style);
                    cell.setCellValue(values[i][4]);
                }
            }
        }
        return wb;
    }

    /**
     * 导出根据监管端id获取所有的检查项目
     *
     * @param sheetName sheet名称
     * @param title     标题
     * @param values    内容
     * @param wb        HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getExportInspectsByGroupId(String sheetName, String[] title, String[][] values, HSSFWorkbook wb) throws Exception {
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        wb.getSheet("项目检查统计").setDefaultRowHeight((short) 700);
        sheet.setColumnWidth(1, 20 * 256);
        sheet.setColumnWidth(2, 20 * 256);
        sheet.setColumnWidth(4, 20 * 256);
        sheet.setColumnWidth(5, 20 * 256);
        style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        style.setWrapText(true);//自动换行

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }
        //创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < values[i].length; j++) {
                //将内容按顺序赋给对应的列对象
                if (1 == j && i + 1 > 1) {
                    FileOutputStream fileOut = null;
                    BufferedImage bufferImg = null;
                    ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                    if (values[i - 1][4] != null && values[i - 1][4] != "") {
                        URL url = new URL(values[i - 1][4]);
                        bufferImg = ImageIO.read(url);
                        ImageIO.write(bufferImg, "jpg", byteArrayOut);

                        //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
                        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                        //anchor主要用于设置图片的属性
                        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 4, i, (short) 5, i + 1);
                        //插入图片
                        patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                    }
                } else {
                    row = sheet.createRow(i + 1);
                    cell = row.createCell(0);
                    cell.setCellStyle(style);
                    cell.setCellValue(values[i][0]);

                    cell = row.createCell(1);
                    cell.setCellStyle(style);
                    cell.setCellValue(values[i][1]);

                    cell = row.createCell(2);
                    cell.setCellStyle(style);
                    cell.setCellValue(values[i][2]);

                    cell = row.createCell(3);
                    cell.setCellStyle(style);
                    cell.setCellValue(values[i][3]);

                    cell = row.createCell(4);
                    cell.setCellStyle(style);
                    cell.setCellValue(values[i][4]);

                    cell = row.createCell(5);
                    CellStyle redStyle = wb.createCellStyle();
                    HSSFFont redFont = wb.createFont();
                    redFont.setColor(HSSFColor.RED.index);
                    redStyle.setFont(redFont);
                    redStyle.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
                    redStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
                    redStyle.setWrapText(true);//自动换行
                    cell.setCellStyle(redStyle);
                    cell.setCellValue(values[i][5]);
                }
            }
        }
        return wb;
    }

}

